package cz.tpj.database.mesto;


import cz.tpj.database.DatabaseConnector;
import cz.tpj.database.obrazky.Obrazok;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import cz.tpj.util.ErrorDatabase;

/**
 * Třída podle vzoru <i>jedináček</i>. Pro získání je třeba zavolat statickou metodu
 * {@link #getInstance()}.
 * @author jabo
 */
public class Table_city {
    //------------------finalni promenne----------------------------------------
    public static final String TABLE       = "mesto";
    public static final String ID          = "id";
    public static final String NAME        = "meno";
    public static final String EMPLOYEE_ID = "zamestnanec_id";
    public static final String SHORT_DESC  = "kratky_popis";
    public static final String LONG_DESC   = "dlhy_popis";
    public static final String MAP_NAME    = "mapy_nazov";
    //
    private static Table_city im = null;
    private Connection con;
    private Statement stmt = null;
    //-------------------------------------------------------------------------
    //======================konstruktor================
    private Table_city(){}

    /**
     * Vrací jedinou instanci této třídy.
     * @return Table_city
     */
    public static Table_city getInstance(){
        if(im == null){
            im = new Table_city();
        }
        return im;
    }
    //=======================metody-------------------------
    private void databaseConnect() throws SQLException{
        DatabaseConnector dc = DatabaseConnector.getInstance();
        con = dc.getConnection();
        stmt = con.createStatement();
    }
    //--------------------------------------------------------------------------
    
    /**
     * Načte jmena a identifikační čísla všech měst a pro každé město vytvoří v
     * ArrayListu instanci typu {@link cz.tpj.database.mesto.City}
     * @return ArrayList<City>
     */
    
    public ArrayList<City> getAllCity() throws ErrorDatabase {
        ResultSet rs = null;
        ArrayList<City> score = new ArrayList<City>();
        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT * FROM "+TABLE+";");

            City city;
            String id, name, shortDesr, longDesc, emplId, mapName;
            while(rs.next()){
                city = new City();
                //nacteni
                id        = rs.getString(ID);
                name      = rs.getString(NAME);
                shortDesr = rs.getString(SHORT_DESC);
                longDesc  = rs.getString(LONG_DESC);
                emplId    = rs.getString(EMPLOYEE_ID);
                mapName   = rs.getString(MAP_NAME);
                //vlozeni
                city.setId(id);
                city.setName(name);
                city.setshortDescription(shortDesr);
                city.setlongDescription(longDesc);
                city.setZamestnanec(emplId);
                city.setMapyNazov(mapName);
                //pridani do kolekce
                score.add(city);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_city.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return score;
    }
    
    /**
     * kontroluje ci mesto s rovnakym menom uz nie je v databazi
     * ak je tak vracia true
     * 
     * @param vkladane
     * @return
     * @throws ErrorDatabase
     */
    public boolean ExistujeMesto(City vkladane) throws ErrorDatabase {
    	boolean ret = false;
        City city = null;
        ResultSet rs = null;

        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT * FROM "+TABLE+";");
            con.commit();

            String id, name, shortDesr, longDesc, emplId, mapName;
            if(rs.next()){
                //nacteni
                name      = rs.getString(NAME);
                if (name == vkladane.getName()) {
                	//take mesto uz existuje
                	ret = true;
                }
            }
        } catch (SQLException ex) {
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Chyba při načítání dat z databáze");
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
    	return ret;
    }
    
    /**
     * vracia posledne mesto v databaze, jeho id
     * @return
     * @throws ErrorDatabase
     */
    public String getLastCity() throws ErrorDatabase {
        ResultSet rs = null;
        String cityId = null;
        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT * FROM "+TABLE+" ORDER BY id DESC LIMIT 0,1;");

            while(rs.next()){
                //nacteni
                cityId = rs.getString(ID);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_city.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return cityId;
    }

    
    /**
     * vlozi mesto do databaze podla udajov zadanych v parametri vkladane
     * vracia false pokial transakcia zlyha, inak vrati true.
     * @param vkladane
     * @return
     * @throws cz.tpj.util.ErrorDatabase
     */

    public String insertCity(City vkladane) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        String ret = null;
        
        //kontroluj ci uz tam take mesto nie je
        boolean ok = false;
        ok = ExistujeMesto(vkladane);
        
        if (ok == false) {
        	//mesto neexistuje, vkladaj ho tam
	        try {
	            databaseConnect();                       
	            String s = "INSERT INTO " + TABLE + "" +
	                    "(" + NAME + "," + EMPLOYEE_ID + "," + SHORT_DESC + "," + LONG_DESC + "," + MAP_NAME + ")" +
	                    "VALUES ('" + vkladane.getName() + "',1,'" + vkladane.getshortDescription() + "','"
	                    + vkladane.getlongDescription() + "', '" + vkladane.getMapyNazov() + "')";
	
	            int updated = stmt.executeUpdate(s);
	            //pokud
	            //1) byl zmenen jeden radek, pak potvrzeni transakce
	            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
	            if(updated == 1){
	                con.commit(); //potvrzeni transakce
	                ok = true;
	                
	                //vrat id novo pridaneho mesta
	                ret = getLastCity();
	            }else{
	                con.rollback(); //vraceni transakce
	                ok = false;
	            }
	        } catch (SQLException ex) {
	            String s = ex.getMessage();
	            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem." + s);
	        } finally{
	            try {
	                stmt.close();
	            } catch (SQLException ex) {}
	        }
        } else {
        	throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Take mesto uz existuje.");
        }
        return ret;
    }
    
    /**
     * vyberie mesto z databazy podla id zadaneho v parametri. 
     * @param idCity
     * @return
     * @throws cz.tpj.util.ErrorDatabase
     */

    public City getCityNameFromId(String idCity) throws ErrorDatabase {
        City city = null;
        ResultSet rs = null;

        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT * FROM "+TABLE+" " +
                                   "WHERE "+ID+" = "+idCity+";");
            con.commit();

            String id, name, shortDesr, longDesc, emplId, mapName;
            if(rs.next()){
                city = new City();
                //nacteni
                id        = rs.getString(ID);
                name      = rs.getString(NAME);
                shortDesr = rs.getString(SHORT_DESC);
                longDesc  = rs.getString(LONG_DESC);
                emplId    = rs.getString(EMPLOYEE_ID);
                mapName   = rs.getString(MAP_NAME);
                //vlozeni
                city.setId(id);
                city.setName(name);
                city.setshortDescription(shortDesr);
                city.setlongDescription(longDesc);
                city.setZamestnanec(emplId);
                city.setMapyNazov(mapName);
            }
        } catch (SQLException ex) {
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Chyba při načítání dat z databáze");
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return city;
    }
    /*
     public City getCityNameFromId(String idCity) throws ErrorDatabase {
        City city = null;
        ResultSet rs = null;

        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT "+ID+", "+ NAME +" FROM "+TABLE+" " +
                                   "WHERE "+ID+" = "+idCity+";");

            String id, name;
            if(rs.next()){
                id = rs.getString(ID);
                name = rs.getString(NAME);
                city = new City(id, name);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_city.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return city;
    }
     */
    
    /**
     * vracia pole miest, ktore splnili podmienku pre vyber specifikovanu
     * parametrom podmienka. Podmienka je sucast sql dotazu, konkretne sekcie 
     * where
     * @param podmienka
     * @return ArrayList<City>
     * @throws cz.tpj.util.ErrorDatabase
     */
    
    public ArrayList<City> getCondCity(String podmienka) throws ErrorDatabase {
        ResultSet rs = null;
        ArrayList<City> score = new ArrayList<City>();
        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT a.id, a.meno, a.kratky_popis, a.dlhy_popis, a.mapy_nazov, a.zamestnanec_id " +
                    " FROM mesto a " +
                    " WHERE 1 AND " + podmienka +
                    " GROUP BY a.id ORDER BY a.meno"
            );
            
            while(rs.next()){
                String id = rs.getString(1);
                String meno = rs.getString(2);
                String kratky_popis = rs.getString(3);
                String dlhy_popis = rs.getString(4);
                String mapy_nazov = rs.getString(5);
                String zamestnanec = rs.getString(6);

                ResultSet rs_sub = null;
                rs_sub = stmt.executeQuery("SELECT a.id, a.cesta " +
                        " FROM obrazky a " +
                        " LEFT JOIN (obrazok_mesto b, mesto c) ON " +
                        " (a.id = b.obrazky_id AND b.mesto_id = c.id)" +
                        " WHERE c.id = " + id
                );
                Obrazok obrazky = new Obrazok();
                while (rs_sub.next()) {
                    String o_id = rs_sub.getString(1);
                    String o_cesta = rs_sub.getString(2);
                    obrazky = new Obrazok(o_id, o_cesta);
                }
                City temp = new City(id, meno, kratky_popis, dlhy_popis, mapy_nazov, obrazky, zamestnanec);
                score.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_city.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }

        }
        return score;
    }
    
    /**
     * zmaze mesto z databaze, ktore ma rovnake id ako je zadane parametrom id.
     * ak je navratova hodnota true, transakcia bola uspesna, inak vracia false.
     * @param id
     * @return boolean
     * @throws cz.tpj.util.ErrorDatabase
     */
    
    public boolean deleteCity(int id) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        try {
            databaseConnect();
            String s = "DELETE FROM " + TABLE + " WHERE " + ID + " = " + id;
            int updated = stmt.executeUpdate(s);
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }
    
    /**
     * aktualizuje mesto v databaze. Je aktualizovane to mesto, ktore ma rovnake 
     * id ako je id objektu typu City predaneho parametrom upravovane.
     * Vrati false ak je transakcia neuspesna, inak metoda vrati true.
     * @param upravovane
     * @return
     * @throws cz.tpj.util.ErrorDatabase
     */
    
    public boolean updateCity(City upravovane) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        try {
            databaseConnect();                                    
            
            String s = "UPDATE " + TABLE + " SET " + 
            		NAME + " = '" + upravovane.getName() + "', " + 
                    SHORT_DESC + " = '" + upravovane.getshortDescription() + "', " + 
                    LONG_DESC + " = '" + upravovane.getlongDescription() + "', " + 
                    MAP_NAME + " = '" + upravovane.getMapyNazov() + "', " + 
                    EMPLOYEE_ID + " = '" + upravovane.getZamestnanec() + "'" + 
                    "WHERE "+ID+"="+upravovane.getId()+";";
                        
            int updated = stmt.executeUpdate(s);
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }


}
